﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CLEX.DataContracts;

namespace CLEX.Server.DAL
{
    public sealed class SharedDatabaseDAL : ISharedDatabaseDAL
    {
        private const string DATABASE_ID_COL = "database_id";
        private const string SHARED_USER_ID_COL = "shared_user_id";

        private const string GET_ALL_SP = "shared_database_get_all";
        private const string GET_SP = "shared_database_get";
        private const string CREATE_SP = "shared_database_create";
        private const string UPDATE_SP = "shared_database_update";
        private const string DELETE_SP = "shared_database_delete";

        private readonly string connectionString;

        public SharedDatabaseDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public List<SharedDatabaseData> GetAll()
        {
            List<SharedDatabaseData> result = new List<SharedDatabaseData>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_ALL_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int databaseIdCol = reader.GetOrdinal(DATABASE_ID_COL);
                        int sharedUserIdCol = reader.GetOrdinal(SHARED_USER_ID_COL);

                        while (reader.Read())
                        {
                            result.Add(new SharedDatabaseData()
                            {
                                DatabaseId = reader.GetInt32(databaseIdCol),
                                SharedUserId = reader.GetInt32(sharedUserIdCol),
                            });
                        }
                    }
                }
            }

            return result;
        }

        public SharedDatabaseData Get(int databaseId, int sharedUserId)
        {
            SharedDatabaseData result;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, databaseId);
                    cmd.Parameters.AddWithValue(SHARED_USER_ID_COL, sharedUserId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int databaseIdCol = reader.GetOrdinal(DATABASE_ID_COL);
                        int sharedUserIdCol = reader.GetOrdinal(SHARED_USER_ID_COL);

                        reader.Read();
                        result = new SharedDatabaseData()
                        {
                            DatabaseId = reader.GetInt32(databaseIdCol),
                            SharedUserId = reader.GetInt32(sharedUserIdCol),
                        };
                    }
                }
            }

            return result;
        }

        public SharedDatabaseData Create(SharedDatabaseData sharedDatabase)
        {
            SharedDatabaseData result = new SharedDatabaseData();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(CREATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, sharedDatabase.DatabaseId);
                    cmd.Parameters.AddWithValue(SHARED_USER_ID_COL, sharedDatabase.SharedUserId);
                    cmd.ExecuteNonQuery();
                }
            }

            result.DatabaseId = sharedDatabase.DatabaseId;
            result.SharedUserId = sharedDatabase.SharedUserId;
            return result;
        }

        public void Update(SharedDatabaseData sharedDatabase)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(UPDATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, sharedDatabase.DatabaseId);
                    cmd.Parameters.AddWithValue(SHARED_USER_ID_COL, sharedDatabase.SharedUserId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void Delete(SharedDatabaseData sharedDatabase)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(DELETE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, sharedDatabase.DatabaseId);
                    cmd.Parameters.AddWithValue(SHARED_USER_ID_COL, sharedDatabase.SharedUserId);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
